import pandas as pd
import markdown
import os
import datetime
from django.contrib import messages
from django.contrib.auth.decorators import login_required
from django.shortcuts import redirect, get_object_or_404, render

from django.http import HttpResponse, Http404
from django.views.decorators.csrf import csrf_exempt
from .models import Mapping, Settlement, Category
from django.db import models
from django.db.models import Q
from django.conf import settings
from django.core.paginator import Paginator
from django.contrib.auth import authenticate, login, logout
from django.utils import timezone

def standardmac(mac):
    result = []
    for item in mac:
        if isinstance(item, str):
            processed_mac = item.replace(':', '').replace('：', '').replace('-', '').replace('.', '').replace(' ', '').upper()
            if len(processed_mac) == 12 and all(c in '0123456789ABCDEF' for c in processed_mac):
                result.append(processed_mac)
            else:
                result.append('错误MAC')
        else:
            result.append('错误MAC')
    return result


def standardsn(sn):
    result = []
    for item in sn:
        if isinstance(item, str):
            processed_sn = item.replace('-', '').replace('：', '').replace(' ', '').replace('.', '').upper()
            if len(processed_sn) == 12 and all(c in '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' for c in processed_sn):
                result.append(processed_sn)
            else:
                result.append('错误SN')
        else:
            result.append('错误SN')
    return result

@login_required
def index(request):
    try:
        # 从session中获取上次登录时间
        previous_login = request.session.get('previous_login')
        return render(request, 'index.html', {'last_login': previous_login})
    except:
        return render(request, 'index.html')

def read_file(uploaded_file):

    file_extension = uploaded_file.name.split('.')[-1].lower()
    try:
        if file_extension in ['xls', 'xlsx']:
            return pd.read_excel(uploaded_file)
        elif file_extension == 'csv':
            return pd.read_csv(uploaded_file)
        else:
            return None
    except Exception as e:
        print(f"读取文件出错: {str(e)}")
        return None

@login_required
def upload_mapping(request):
    if request.method == 'POST' and request.FILES.get('file'):
        uploaded_file = request.FILES['file']
        df = read_file(uploaded_file)
        if df is None:
            return HttpResponse("不支持的文件类型或读取文件出错", status=400)
        
        # 检查 df 的列是否符合要求
        required_columns = {'ID', 'MAC', 'SN'}
        if set(df.columns) != required_columns:
            message = "数据列不符合要求，应包括且仅包括 ID、MAC、SN 列。"
            return render(request, 'upload_mapping.html', {'message': message})

                
        df['MAC'] = standardmac(df['MAC'])
        df['SN'] = standardsn(df['SN'])

        existing_macs = set(Mapping.objects.values_list('mac', flat=True))
        
        df['是否已存在'] = df['MAC'].apply(lambda x: '重复数据' if x in existing_macs else '')
        data = df.to_dict(orient='records')
        request.session['preview_data'] = data
        return render(request, 'upload_mapping_success.html', {'data': data})
    
    return render(request, 'upload_mapping.html')

@login_required
def import_mapping(request):
    if request.method == 'POST' and 'preview_data' in request.session:
        # 从会话获取预览数据
        preview_data = request.session.get('preview_data')
        # 过滤掉错误MAC、错误SN和是否已存在标记为重复数据的记录
        valid_data = [
            record for record in preview_data
            if record['MAC'] != '错误MAC' and 
               record['SN'] != '错误SN' and 
               record['是否已存在'] != '重复数据'
        ]
        # 批量创建 Mapping 实例
        mapping_instances = [
            Mapping(mac=record['MAC'], sn=record['SN'])
            for record in valid_data
        ]
        Mapping.objects.bulk_create(mapping_instances)
        # 清除会话中的预览数据
        del request.session['preview_data']
        return render(request, 'index.html', {'message': '映射表数据写入成功数据库'})
    return render(request, 'index.html', {'message': '映射表数据写入失败数据库'})

@login_required
def upload_settlement(request):
    if request.method == 'POST' and request.FILES.get('file'):
        uploaded_file = request.FILES['file']
        df = read_file(uploaded_file)
        if df is None:
            return HttpResponse("不支持的文件类型或读取文件出错", status=400)
        
        # 检查 df 的列是否符合要求
        required_columns = {'ID', 'MAC', '终端来源', '终端类型', '结算金额', '结算日期'}
        if set(df.columns) != required_columns:
            message = "数据列不符合要求，应包括且仅包括 ID、MAC、终端来源、终端类型、结算金额、结算日期 列。"
            return render(request, 'upload_settlement.html', {'message': message})        

        df['MAC'] = standardmac(df['MAC'])
        df['MAC错误'] = df['MAC'].apply(lambda x: 'MAC错误' if x == '错误MAC' else '')

        # 检查 MAC 列是否存在重复值
        mac_duplicated = df['MAC'].duplicated(keep=False)
        df.loc[mac_duplicated, 'MAC错误'] = df.loc[mac_duplicated, 'MAC错误'].apply(lambda x: x + "表内存在重复值" if x else "表内存在重复值")

        # 检查终端类型列
        if df['终端类型'].isnull().any():
            message = "终端类型存在空值，无法导入，请先补充填写终端类型"
            return render(request, 'upload_settlement.html', {'message': message})

        # 获取 Category 模型中所有 type 值
        category_types = set(Category.objects.values_list('type', flat=True))
        invalid_types = df[~df['终端类型'].isin(category_types)]['终端类型'].unique()
        if invalid_types.size > 0:
            message = "导入表中存在无法识别的终端类型，请先补充终端类别表 "
            return render(request, 'upload_settlement.html', {'message': message})

        # 构建类型到类别的映射
        type_to_category = {cat.type: cat.category for cat in Category.objects.all()}
        df['类别'] = df['终端类型'].map(type_to_category)
        valid_sources = {'购置', '赠送', '翻新', '其他'}
        df['来源错误'] = df['终端来源'].apply(lambda x: '来源错误' if x not in valid_sources and x != '错误' else '')
        df['结算金额'] = pd.to_numeric(df['结算金额'], errors='coerce').apply(
            lambda x: f'{x:.2f}' if pd.notna(x) else '错误')
        df['结算日期'] = pd.to_datetime(df['结算日期'], errors='coerce')
        df['结算日期'] = df['结算日期'].dt.date
        df['结算日期'] = df['结算日期'].astype(str)
        df['日期错误'] = df['结算日期'].apply(lambda x: '日期错误' if x == 'NaT' else '')
        existing_macs = set(Settlement.objects.values_list('mac', flat=True))
        df['重复提示'] = df['MAC'].apply(lambda x: '重复数据' if x in existing_macs else '')
        data = df.to_dict(orient='records')

        # 检查是否满足条件
        mac_errors_empty = (df['MAC错误'] == '').all()
        source_errors_empty = (df['来源错误'] == '').all()
        date_errors_empty = (df['日期错误'] == '').all()
        repeat_hints_empty = (df['重复提示'] == '').all()
        repeat_with_special_source = (df['重复提示'] == '重复数据') & df['终端来源'].isin(['翻新', '其他'])

        if (mac_errors_empty and source_errors_empty and date_errors_empty) and (repeat_hints_empty or repeat_with_special_source.any()):
            source_mapping = {
                '购置': 1,
                '赠送': 2,
                '翻新': 3,
                '其他': 4
            }
            df['终端来源'] = df['终端来源'].map(source_mapping).fillna('错误')
            data_standard = df.to_dict(orient='records')
            request.session['preview_data'] = data_standard
        else:
            request.session['preview_data'] = False

        return render(request, 'upload_settlement_success.html', {'data': data})
    return render(request, 'upload_settlement.html')

@login_required
def import_settlement(request): 
    if request.method == 'POST' and 'preview_data' in request.session:
        data = request.session.get('preview_data', [])
        if not data:
            return render(request, 'index.html', {'message': '结算表数据检查未通过，无法导入数据库'})

        df = pd.DataFrame(data)
        new_data = df[(df['终端来源'].isin([1, 2]))]
        fix_data_noduplicate = df[(df['终端来源'].isin([3])) & (df['重复提示'] != '重复数据')]
        fix_data_duplicate = df[((df['终端来源'].isin([3])) & (df['重复提示'] == '重复数据'))]
        other_data = df[(df['终端来源'].isin([4]))]


        # 处理更新操作
        if not new_data.empty:
            settlement_instances = []
            for _, row in new_data.iterrows():
                settlement = Settlement(
                    mac=row['MAC'],
                    source=row['终端来源'],
                    type=row['终端类型'],
                    amount=row['结算金额'],
                    date0=row['结算日期'],
                    category=row['类别'],
                )
                settlement_instances.append(settlement)
            Settlement.objects.bulk_create(settlement_instances)

        if not fix_data_noduplicate.empty:
            settlement_instances = []
            for _, row in fix_data_noduplicate.iterrows():
                settlement = Settlement(
                    mac=row['MAC'],
                    source=row['终端来源'],
                    type=row['终端类型'],
                    amount=row['结算金额'],
                    date1=row['结算日期'],
                    fix=1,
                    category=row['类别'],
                )
                settlement_instances.append(settlement)
            Settlement.objects.bulk_create(settlement_instances)

        if not fix_data_duplicate.empty:
            for _, row in fix_data_duplicate.iterrows():
                mac = row['MAC']
                try:
                    Settlement.objects.filter(mac=mac).update(
                        source=row['终端来源'],
                        amount=row['结算金额'],
                        date1=row['结算日期'],
                        times=0,
                        mismapping=0,
                        blank=0,
                        online=0,
                        offline=0,
                        status=1,
                        category=row['类别'],
                    )
                    # 单独更新 fix 字段，实现自增
                    Settlement.objects.filter(mac=mac).update(fix=models.F('fix') + 1)
                except Settlement.DoesNotExist:
                    continue

        if not other_data.empty:
            settlement_instances = []
            for _, row in other_data.iterrows():
                settlement = Settlement(
                    mac=row['MAC'],
                    source=row['终端来源'],
                    amount=row['结算金额'],
                    date1=row['结算日期'],
                    category=row['类别'],
                )
                settlement_instances.append(settlement)
            Settlement.objects.bulk_create(settlement_instances)

        # 清除会话中的预览数据
        del request.session['preview_data']
        return render(request, 'index.html', {'message': '结算表数据写入成功数据库'})
    return render(request, 'index.html', {'message': '结算表数据写入失败数据库'})


@login_required
def upload_management(request):
    if request.method == 'POST' and request.FILES.get('file'):
        uploaded_file = request.FILES['file']
        df = read_file(uploaded_file)
        if df is None:
            return HttpResponse("不支持的文件类型或读取文件出错", status=400)
        
        # 检查 df 的列是否符合要求
        required_columns = {'SN', '运行状态'}
        if set(df.columns) != required_columns:
            message = "数据列不符合要求，应包括且仅包括 SN、运行状态 列。"
            return render(request, 'upload_management.html', {'message': message})

        df['SN'] = standardsn(df['SN'])
        df = df[df['SN'] != '错误SN']
        # 定义去重函数
        def process_group(group):
            if any(group['运行状态'].str.contains('在线|online', case=False, na=False)):
                # 存在在线状态，保留该记录并设置状态为在线
                record = group.iloc[0].copy()
                record['运行状态'] = '在线'
                return record
            else:
                # 不存在在线状态，保留第一条记录并设置状态为离线
                record = group.iloc[0].copy()
                record['运行状态'] = '离线'
                return record

        # 按 SN 分组并应用去重函数
        df = df.groupby('SN').apply(process_group).reset_index(drop=True)

        data = df.to_dict(orient='records')
        request.session['preview_data'] = data
        return render(request, 'upload_management_success.html', {'data': data})
    
    return render(request, 'upload_management.html')

@login_required
def import_management(request):
    if request.method == 'POST' and 'preview_data' in request.session:
        # 从会话获取预览数据
        preview_data = request.session.get('preview_data')
        # 验证 preview_data 类型
        if not isinstance(preview_data, list):
            return render(request, 'index.html', {'message': '预览数据格式错误，请联系管理员'})

        # 筛选出核查状态为待查或疑似损失且 type 在 cat_types 列表中的记录
        # cat_types = Category.objects.filter(category='光猫').values_list('type', flat=True)
        settlements = Settlement.objects.filter(
            (Q(status=1) | Q(status=2)) & Q(category='光猫')
        )
        settlements_to_update = []

        for settlement in settlements:
            # 检查次数加1
            settlement.times += 1
            try:
                mapping = Mapping.objects.get(mac=settlement.mac)
                sn = mapping.sn
                match_found = False
                for data in preview_data:
                    # 验证 data 字典包含所需键
                    if 'SN' in data and '运行状态' in data and data['SN'] == sn:
                        match_found = True
                        if data['运行状态'] in ['在线', 'online']:
                            # 在线次数加1，核查状态设为确认正常
                            settlement.online += 1
                            settlement.status = 4
                        else:
                            # 离线次数加1
                            settlement.offline += 1
                            if settlement.offline > 2:
                                # 离线次数大于2，核查状态设为疑似损失
                                settlement.status = 2
                        break
                if not match_found:
                    # 未找到匹配信息，未纳管次数加1，核查状态设为疑似损失
                    settlement.blank += 1
                    settlement.status = 2
            except Mapping.DoesNotExist:
                # 未找到Mac地址对照表记录，映射错误字段加1
                settlement.mismapping += 1
            except Exception as e:
                print(f"处理结算记录 {settlement.id} 时出错: {str(e)}")

            settlements_to_update.append(settlement)

        # 批量更新数据库
        Settlement.objects.bulk_update(settlements_to_update, [
            'times', 'blank', 'online', 'offline', 'status', 'mismapping'
        ])

        # 清除会话中的预览数据
        del request.session['preview_data']
        return render(request, 'index.html', {'message': '检查完成，数据已写入数据库'})
    return render(request, 'index.html', {'message': '检查失败请联系管理员'})

@login_required
def download_template(request, filename):
    file_path = os.path.join(settings.MEDIA_ROOT, filename)
    if os.path.exists(file_path):
        with open(file_path, 'rb') as fh:
            response = HttpResponse(fh.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            response['Content-Disposition'] = 'inline; filename=' + os.path.basename(file_path)
            return response
    raise Http404

@login_required
def export_mapping(request):
    # 获取 Mapping 模型的所有数据
    mapping_data = Mapping.objects.all().values()
    df = pd.DataFrame(mapping_data)

    # 获取每个字段的 verbose_name 作为表头
    field_verbose_names = {
        field.name: field.verbose_name for field in Mapping._meta.get_fields() if field.name in df.columns
    }
    df = df.rename(columns=field_verbose_names)
    
    # 设置响应头
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    today = datetime.datetime.now().strftime("%Y%m%d")
    filename = f"mapping_{today}.xlsx"
    response['Content-Disposition'] = f'attachment; filename={filename}'
    
    # 将数据写入 Excel 文件
    df.to_excel(response, index=False)
    return response

@login_required
def export_settlement(request):
    # 获取 Settlement 模型的所有数据
    settlement_data = Settlement.objects.all().values()
    df = pd.DataFrame(settlement_data)

    # 处理 choice 字段，将其转换为选项名称
    for field in Settlement._meta.get_fields():
        if hasattr(field, 'choices') and field.choices:
            df[field.name] = df[field.name].apply(
                lambda x: dict(field.choices).get(x, x) if x is not None else x
            )

    # 获取每个字段的 verbose_name 作为表头
    field_verbose_names = {
        field.name: field.verbose_name for field in Settlement._meta.get_fields() if field.name in df.columns
    }
    df = df.rename(columns=field_verbose_names)

    # 设置响应头
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    today = datetime.datetime.now().strftime("%Y%m%d")
    filename = f"settlement_{today}.xlsx"
    response['Content-Disposition'] = f'attachment; filename={filename}'

    # 将数据写入 Excel 文件
    df.to_excel(response, index=False)
    return response

@login_required
def settlement_list(request):
    settlements = Settlement.objects.all()
    # 获取筛选和搜索参数
    source = request.GET.get('source')
    category = request.GET.get('category')
    status = request.GET.get('status')
    mac = request.GET.get('mac')

    # 进行筛选和搜索
    if source:
        try:
            source = int(source)
            settlements = settlements.filter(source=source)
        except ValueError:
            pass
    if status:
        try:
            status = int(status)
            settlements = settlements.filter(status=status)
        except ValueError:
            pass
    if category:
        settlements = settlements.filter(category=category)
    if mac:
        settlements = settlements.filter(mac__icontains=mac)

    settlements = settlements.order_by('mac')

    # 初始化 Paginator，每页显示 50 条记录
    paginator = Paginator(settlements, 50)
    page_number = request.GET.get('page')
    page_obj = paginator.get_page(page_number)

    # 获取所有类别用于筛选
    categories = Category.objects.values_list('category', flat=True).distinct()

    # 获取 source 和 status 的 choices，添加存在性检查
    source_choices = getattr(Settlement, 'SOURCE_CHOICES', [])
    status_choices = getattr(Settlement, 'STATUS_CHOICES', [])

    return render(request, 'settlement_list.html', {
        'page_obj': page_obj,
        'categories': categories,
        'source_choices': source_choices,
        'status_choices': status_choices
    })

@login_required
def edit_settlement(request, pk):
    settlement = get_object_or_404(Settlement, pk=pk)
    if request.method == 'POST':
        settlement.manual = request.POST.get('manual')
        settlement.status = request.POST.get('status')
        settlement.save()
        return redirect('settlement_list')

    # 获取 source 和 status 的 choices，添加存在性检查
    status_choices = getattr(Settlement, 'STATUS_CHOICES', [])
    return render(request, 'edit_settlement.html', {
        'settlement': settlement,
        'status_choices': status_choices
    })

@login_required
def category_list(request):
    """显示 Category 列表"""
    categories = Category.objects.all()
    return render(request, 'category_list.html', {'categories': categories})

def create_category(request):
    """创建新的 Category 记录"""
    if request.method == 'POST':
        type_value = request.POST.get('type')
        category_value = request.POST.get('category')
        if type_value and category_value:
            Category.objects.create(type=type_value, category=category_value)
            messages.success(request, '类别创建成功。')
            return redirect('category_list')
        else:
            messages.error(request, '终端类型和类别不能为空。')
    return render(request, 'create_category.html')

def user_login(request):
    """用户登录视图"""
    if request.method == 'POST':
        username = request.POST.get('username')
        password = request.POST.get('password')
        try:
            user = authenticate(username=username, password=password)
            
            if user is not None:
                # 在登录前获取上次登录时间
                previous_login = timezone.localtime(user.last_login).strftime('%Y-%m-%d %H:%M:%S') if user.last_login else None
                
                # 执行登录操作（Django会自动更新last_login）
                login(request, user)
                
                request.session['previous_login'] = previous_login
                messages.success(request, f'系统登录成功')
                return redirect('index')
            else:
                messages.warning(request, f"用户名或密码错误")
                return redirect('login')
        except Exception as e:
            messages.error(request, f"系统错误：{e}，请稍后重试")
            return redirect('login')
    return render(request, 'login.html') 

def user_logout(request):
    """用户登出视图"""
    try:
        logout(request)
        request.session.flush()
    except Exception as e:
        messages.error(request, f"登出过程中出现错误，请稍后重试")
    return render(request, 'logout.html')

@login_required
def readme(request):
    try:
        markdown_file_path = os.path.join(os.getcwd(), 'readme.md')
        
        with open(markdown_file_path, 'r', encoding='utf-8') as file:
            markdown_content = file.read()
        
        html_content = markdown.markdown(markdown_content)

        template_path = 'help_page.html'
        
        return render(request, template_path, {'html_content': html_content})
    except FileNotFoundError as e:
        return HttpResponse("Markdown 文件未找到。", status=404)
    except Exception as e:
        return HttpResponse(f"发生错误: {str(e)}", status=500) 